﻿#include "DBManage.h"
#include <QFile>
#include <QDebug>
#include <QMessageBox>

DBManage* DBManage::m_pInstance = nullptr;
DBManage::DBManage(){
    //    connectDB();
}

DBManage::~DBManage()
{
    if (m_pInstance != NULL)
    {
        delete m_pInstance;
        m_pInstance = NULL;
    }
}
DBManage *DBManage::getInstance()
{
    if(m_pInstance == nullptr)
        m_pInstance = new DBManage;
    return m_pInstance;
}

bool DBManage::connectDB()
{
    m_database = QSqlDatabase::addDatabase("QSQLITE");
    m_database.setDatabaseName("E:/1-Projects/2-PersonalProject/2-MixHistoryProj/1-1-SeeHistory/HistoryDBManager/Config/History.db");
//    m_database.setDatabaseName("Config/History.db");

    //    qDebug() << m_database.databaseName();

    m_sql_query = QSqlQuery(m_database);
    if (!m_database.open())
    {
        qDebug() << "Error: Failed to connect database." << m_database.lastError();
        return false;
    }
    else
    {
        qDebug() << "Succeed to connect database." ;
        return true;
    }
}

// 设置数据库
void DBManage::setDatabase(QString dbPath)
{
    m_database.close();
    m_sql_query.clear();

    m_database.setDatabaseName(dbPath);
    m_sql_query = QSqlQuery(m_database);
    if (!m_database.open())
    {
        qDebug() << "Error: Failed to connect database." << m_database.lastError();
        return;
    }
    else
    {
        qDebug() << "Succeed to connect database." ;
        emit sigRefreshAllView();
        return;
    }
}

// 获取当前数据库名称
QString DBManage::getCurrentDBName()
{
    return m_database.databaseName();
}

bool DBManage::exceSQL(QString sql)
{
    if(m_database.isOpen())
        m_database.open();
//    m_sql_query = QSqlQuery(m_database);
    //打开外键 实现级联删除
    m_sql_query.exec("PRAGMA foreign_keys = ON");
    if(!m_sql_query.exec(sql))
    {
        qDebug() << __FUNCTION__ << __LINE__ << sql << m_sql_query.lastError();
        return false;
    }
    else
    {
        return true;
    }
}

bool DBManage::addDynastyData(int startTime, int endTime, QString name, QString id)
{
    QString sql = QString("INSERT INTO Dynasty VALUES(%1, %2, '%3', '%4');").arg(startTime).arg(endTime).arg(name).arg(id);
    return exceSQL(sql);
}

bool DBManage::updateDynastyData(int startTime, int endTime, QString name, QString id)
{
    QString sql = QString("UPDATE Dynasty SET StartTime = %1, EndTime = %2, DynastyName = '%3' WHERE DynastyID = '%4';").arg(startTime).arg(endTime).arg(name).arg(id);
    return exceSQL(sql);
}

bool DBManage::deleteDynastyData(QString id)
{
    QString sql = QString("DELETE FROM Dynasty WHERE DynastyID = '%1';").arg(id);
    return exceSQL(sql);
}

QList<Dynasty> DBManage::getDynastyDatas()
{
    QList<Dynasty> lstDynasty;
    QString sql;

    sql = QString("SELECT * FROM Dynasty;");
    if(exceSQL(sql))
    {
        while(m_sql_query.next())
        {
            Dynasty dynasty;
            dynasty.startTime = m_sql_query.value(0).toInt();
            dynasty.endTime = m_sql_query.value(1).toInt();
            dynasty.dynastyName = m_sql_query.value(2).toString();
            dynasty.dynastyID = m_sql_query.value(3).toString();
            lstDynasty.append(dynasty);
        }
    }
    return lstDynasty;
}

QString DBManage::getDynastyNameByID(const QString &id)
{
    QString name = "";
    QString sql = QString("SELECT DynastyName FROM Dynasty WHERE DynastyID = '%1';").arg(id);
    if(exceSQL(sql))
    {
        if(m_sql_query.next())
        {
            name = m_sql_query.value(0).toString();
        }
    }
    return name;
}

bool DBManage::addCountryData(const Country& data)
{
    QString sql = QString("INSERT INTO Country VALUES('%1', %2, %3, '%4');").arg(data.countryID).arg(data.startTime).arg(data.endTime).arg(data.countryName);
    return exceSQL(sql);
}

bool DBManage::updateCountryData(const Country &data)
{
    QString sql = QString("UPDATE Country SET CountryID = '%1', StartTime = %2, EndTime = %3, CountryName = '%4' WHERE CountryID = '%5';").arg(data.countryID).arg(data.startTime).arg(data.endTime).arg(data.countryName).arg(data.countryID);
    return exceSQL(sql);
}

bool DBManage::deleteCountryData(QString id)
{
    QString sql = QString("DELETE FROM Country WHERE CountryID = '%1';").arg(id);
    return exceSQL(sql);
}

QList<Country> DBManage::getCountryDatas()
{
    QList<Country> lstCountry;
    QString sql;

    sql = QString("SELECT * FROM Country;");
    if(exceSQL(sql))
    {
        while(m_sql_query.next())
        {
            Country country;
            country.countryID = m_sql_query.value(0).toString();
            country.startTime = m_sql_query.value(1).toInt();
            country.endTime = m_sql_query.value(2).toInt();
            country.countryName = m_sql_query.value(3).toString();
            lstCountry.append(country);
        }
    }
    return lstCountry;
}

QString DBManage::getCountryNameByID(const QString &id)
{
    QString name = "";
    QString sql = QString("SELECT CountryName FROM Country WHERE CountryID = '%1';").arg(id);
    if(exceSQL(sql))
    {
        if(m_sql_query.next())
        {
            name = m_sql_query.value(0).toString();
        }
    }
    return name;
}

bool DBManage::addRulerData(const Ruler& data)
{
    QString sql = QString("INSERT INTO Ruler VALUES('%1', '%2', '%3', %4, %5, '%6', '%7');").arg(data.rulerID).arg(data.countryID).arg(data.rulerName).arg(data.startTime).arg(data.endTime).arg(data.templeName).arg(data.posthumousName);
    return exceSQL(sql);
}

bool DBManage::updateRulerData(const Ruler &data)
{
    QString sql = QString("UPDATE Ruler SET RulerID = '%1', CountryID = '%2', RulerName = '%3', StartTime = %4, EndTime = %5, TempleName = '%6', PosthumousName = '%7' WHERE RulerID = '%8';").arg(data.rulerID).arg(data.countryID).arg(data.rulerName).arg(data.startTime).arg(data.endTime).arg(data.templeName).arg(data.posthumousName).arg(data.rulerID);
    return exceSQL(sql);
}

bool DBManage::deleteRulerData(QString id)
{
    QString sql = QString("DELETE FROM Ruler WHERE RulerID = '%1';").arg(id);
    return exceSQL(sql);
}

QList<Ruler> DBManage::getRulerDatas()
{
    QList<Ruler> lstRuler;
    QString sql;

    sql = QString("SELECT * FROM Ruler;");
    if(exceSQL(sql))
    {
        while(m_sql_query.next())
        {
            Ruler ruler;
            ruler.rulerID = m_sql_query.value(0).toString();
            ruler.countryID = m_sql_query.value(1).toString();
            ruler.rulerName = m_sql_query.value(2).toString();
            ruler.startTime = m_sql_query.value(3).toInt();
            ruler.endTime = m_sql_query.value(4).toInt();
            ruler.templeName = m_sql_query.value(5).toString();
            ruler.posthumousName = m_sql_query.value(6).toString();
            lstRuler.append(ruler);
        }
    }
    return lstRuler;
}

bool DBManage::addCapitalData(const Capital &data)
{
    QString sql = QString("INSERT INTO Capital VALUES('%1', '%2', '%3', %4, %5);").arg(data.capitalID).arg(data.countryID).arg(data.capitalName).arg(data.startTime).arg(data.endTime);
    return exceSQL(sql);
}

bool DBManage::updateCapitalData(const Capital &data)
{
    QString sql = QString("UPDATE Capital SET CapitalID = '%1', CountryID = '%2', CapitalName = '%3', StartTime = %4, EndTime = %5 WHERE CapitalID = '%6';").arg(data.capitalID).arg(data.countryID).arg(data.capitalName).arg(data.startTime).arg(data.endTime).arg(data.capitalID);
    return exceSQL(sql);
}

bool DBManage::deleteCapitalData(QString id)
{
    QString sql = QString("DELETE FROM Capital WHERE CapitalID = '%1';").arg(id);
    return exceSQL(sql);
}

QList<Capital> DBManage::getCapitalDatas()
{
    QList<Capital> lstCapital;
    QString sql;

    sql = QString("SELECT * FROM Capital;");
    if(exceSQL(sql))
    {
        while(m_sql_query.next())
        {
            Capital capital;
            capital.capitalID = m_sql_query.value(0).toString();
            capital.countryID = m_sql_query.value(1).toString();
            capital.capitalName = m_sql_query.value(2).toString();
            capital.startTime = m_sql_query.value(3).toInt();
            capital.endTime = m_sql_query.value(4).toInt();
            lstCapital.append(capital);
        }
    }
    return lstCapital;
}

bool DBManage::addReignTitleData(const ReignTitle &data)
{
    QString sql = QString("INSERT INTO ReignTitle VALUES('%1', '%2', '%3', %4, %5);").arg(data.reignTitleID).arg(data.countryID).arg(data.reignTitleName).arg(data.startTime).arg(data.endTime);
    return exceSQL(sql);
}

bool DBManage::updateReignTitleData(const ReignTitle &data)
{
    QString sql = QString("UPDATE ReignTitle SET ReignTitleID = '%1', CountryID = '%2', ReignTitleName = '%3', StartTime = %4, EndTime = %5 WHERE ReignTitleID = '%6';").arg(data.reignTitleID).arg(data.countryID).arg(data.reignTitleName).arg(data.startTime).arg(data.endTime).arg(data.reignTitleID);
    return exceSQL(sql);
}

bool DBManage::deleteReignTitleData(QString id)
{
    QString sql = QString("DELETE FROM ReignTitle WHERE ReignTitleID = '%1';").arg(id);
    return exceSQL(sql);
}

QList<ReignTitle> DBManage::getReignTitleDatas()
{
    QList<ReignTitle> lstReignTitle;
    QString sql;

    sql = QString("SELECT * FROM ReignTitle;");
    if(exceSQL(sql))
    {
        while(m_sql_query.next())
        {
            ReignTitle reignTitle;
            reignTitle.reignTitleID = m_sql_query.value(0).toString();
            reignTitle.countryID = m_sql_query.value(1).toString();
            reignTitle.reignTitleName = m_sql_query.value(2).toString();
            reignTitle.startTime = m_sql_query.value(3).toInt();
            reignTitle.endTime = m_sql_query.value(4).toInt();
            lstReignTitle.append(reignTitle);
        }
    }
    return lstReignTitle;
}

bool DBManage::addMapData(const MapData& data)
{
    QString sql = QString("INSERT INTO Map(MapID, DynastyID, MapArea, MapNote, MapImage) VALUES('%1', '%2', '%3', '%4', :imagedata);").arg(data.mapID).arg(data.dynastyID).arg(data.mapArea).arg(data.mapNote);

    m_sql_query.prepare(sql);
    m_sql_query.bindValue(":imagedata", data.mapImage);

    if(!m_sql_query.exec())
    {
        QString sError = m_sql_query.lastError().text();
        sError = "数据库表格插入数据失败:" + sError;
        qDebug() << "err = " << sError;
        return false;

    }
    else
        return true;
}

bool DBManage::updateMapData(const MapData &data)
{
    QString sql = QString("UPDATE Map SET MapID = '%1', DynastyID = '%2', MapArea = '%3', MapNote = '%4', MapImage = :imagedata WHERE MapID = '%5';").arg(data.mapID).arg(data.dynastyID).arg(data.mapArea).arg(data.mapNote).arg(data.mapID);
    m_sql_query.prepare(sql);
    m_sql_query.bindValue(":imagedata", data.mapImage);
    if(!m_sql_query.exec())
    {
        QString sError = m_sql_query.lastError().text();
        sError = "数据库表格更新失败:" + sError;
        qDebug() << "err = " << sError;
        return false;

    }
    else
        return true;
}

bool DBManage::deleteMapData(QString id)
{
    QString sql = QString("DELETE FROM Map WHERE MapID = '%1';").arg(id);
    return exceSQL(sql);
}

QList<MapData> DBManage::getMapDatas()
{
    QList<MapData> lstMapData;
    QString sql;

    sql = QString("SELECT * FROM Map;");
    if(exceSQL(sql))
    {
        while(m_sql_query.next())
        {
            MapData mapData;
            mapData.mapID = m_sql_query.value(0).toString();
            mapData.dynastyID = m_sql_query.value(1).toString();
            mapData.mapArea = m_sql_query.value(2).toString();
            mapData.mapNote = m_sql_query.value(3).toString();
            mapData.mapImage = m_sql_query.value(4).toByteArray();
            lstMapData.append(mapData);
        }
    }
    return lstMapData;
}

MapData DBManage::getMapDataByID(const QString &id)
{
    MapData data;
    QString sql = QString("SELECT * FROM Map WHERE MapID = '%1';").arg(id);
    if(exceSQL(sql))
    {
        if(m_sql_query.next())
        {
            data.mapID = m_sql_query.value(0).toString();
            data.dynastyID = m_sql_query.value(1).toString();
            data.mapArea = m_sql_query.value(2).toString();
            data.mapNote = m_sql_query.value(3).toString();
            data.mapImage = m_sql_query.value(4).toByteArray();
        }
    }
    return data;
}

QList<MapData> DBManage::getMapDatasByDynastyID(const QString &id)
{
    QList<MapData> lstMapData;
    QString sql;

    sql = QString("SELECT * FROM Map WHERE DynastyID = '%1';").arg(id);
    if(exceSQL(sql))
    {
        while(m_sql_query.next())
        {
            MapData mapData;
            mapData.mapID = m_sql_query.value(0).toString();
            mapData.dynastyID = m_sql_query.value(1).toString();
            mapData.mapArea = m_sql_query.value(2).toString();
            mapData.mapNote = m_sql_query.value(3).toString();
            mapData.mapImage = m_sql_query.value(4).toByteArray();
            lstMapData.append(mapData);
        }
    }
    return lstMapData;

}
